from flask import jsonify
from sqlalchemy import text

from tzq.database_compare_system import dcs_bp
from tzq.extensions import db


@dcs_bp.route('/api/initJobTableRepare/<int:id>', methods=['GET'])
def init_job_table_repare(id):
    """
        初始化 模型比对修复 表数据
        1、比对任务，把 “模型比对修复” 的结果插入到 dc_job_table_repare_t 表
    """
    try:
        v_sql = text("""
delete from dc_job_table_repare_t where comp_task_id = :id ;
INSERT INTO dc_job_table_repare_t (comp_task_id, comp_task_name, schema, table_name, table_name_check_result, table_name1, table_name2, compare_scripts_of_source2, table_name3, compare_scripts_of_source3, table_name4, compare_scripts_of_source4, table_name5, compare_scripts_of_source5, table_name6, compare_scripts_of_source6, table_name7, compare_scripts_of_source7)
with constant as (
  select t.comp_task_id,t.comp_task_name
        ,s1.connect_name as source_name1
        ,s2.connect_name as source_name2
        ,s3.connect_name as source_name3
        ,s4.connect_name as source_name4
        ,s5.connect_name as source_name5
        ,s6.connect_name as source_name6
        ,s7.connect_name as source_name7
    from dc_comp_task_t t 
    left join dc_database_info_t s1 on t.db_info_1_id = s1.db_info_id
    left join dc_database_info_t s2 on t.db_info_2_id = s2.db_info_id
    left join dc_database_info_t s3 on t.db_info_3_id = s3.db_info_id
    left join dc_database_info_t s4 on t.db_info_4_id = s4.db_info_id
    left join dc_database_info_t s5 on t.db_info_5_id = s5.db_info_id
    left join dc_database_info_t s6 on t.db_info_6_id = s6.db_info_id
    left join dc_database_info_t s7 on t.db_info_7_id = s7.db_info_id)
, s1 as (select distinct comp_task_name, source, schema, table_name from dc_table_info_t t inner join constant c on t.source = c.source_name1)
, s2 as (select distinct comp_task_name, source, schema, table_name from dc_table_info_t t inner join constant c on t.source = c.source_name2)
, s3 as (select distinct comp_task_name, source, schema, table_name from dc_table_info_t t inner join constant c on t.source = c.source_name3)
, s4 as (select distinct comp_task_name, source, schema, table_name from dc_table_info_t t inner join constant c on t.source = c.source_name4)
, s5 as (select distinct comp_task_name, source, schema, table_name from dc_table_info_t t inner join constant c on t.source = c.source_name5)
, s6 as (select distinct comp_task_name, source, schema, table_name from dc_table_info_t t inner join constant c on t.source = c.source_name6)
, s7 as (select distinct comp_task_name, source, schema, table_name from dc_table_info_t t inner join constant c on t.source = c.source_name7)
,tab_base_info_tmp as (
    select comp_task_name, schema, table_name from s1 UNION
    select comp_task_name, schema, table_name from s2 UNION
    select comp_task_name, schema, table_name from s3 UNION
    select comp_task_name, schema, table_name from s4 UNION
    select comp_task_name, schema, table_name from s5 UNION
    select comp_task_name, schema, table_name from s6 UNION
    select comp_task_name, schema, table_name from s7
    )
,tab_base_info as (
select c.comp_task_id,c.comp_task_name
      ,t.schema,t.table_name 
       -- 表是否存在
      ,case when COALESCE(s1.table_name,'tzqnull') = COALESCE(s2.table_name,'tzqnull') and
			        (COALESCE(s1.table_name,'tzqnull') = COALESCE(s3.table_name,'tzqnull') or ((select count(*) from s3 where s3.comp_task_name = t.comp_task_name) = 0)) and
			        (COALESCE(s1.table_name,'tzqnull') = COALESCE(s4.table_name,'tzqnull') or ((select count(*) from s4 where s4.comp_task_name = t.comp_task_name) = 0)) and
			        (COALESCE(s1.table_name,'tzqnull') = COALESCE(s5.table_name,'tzqnull') or ((select count(*) from s5 where s5.comp_task_name = t.comp_task_name) = 0)) and
			        (COALESCE(s1.table_name,'tzqnull') = COALESCE(s6.table_name,'tzqnull') or ((select count(*) from s6 where s6.comp_task_name = t.comp_task_name) = 0)) and
			        (COALESCE(s1.table_name,'tzqnull') = COALESCE(s7.table_name,'tzqnull') or ((select count(*) from s7 where s7.comp_task_name = t.comp_task_name) = 0))
            then '√' else '×' end as table_name_check_result
      ,case COALESCE(s1.table_name,'tzqnull') when 'tzqnull' then '×' else '√' end as table_name1
      ,case COALESCE(s2.table_name,'tzqnull') when 'tzqnull' then '×' else '√' end as table_name2
      ,case when COALESCE(s3.table_name,'tzqnull') = 'tzqnull' and ((select count(*) from s3 where s3.comp_task_name = t.comp_task_name) > 0) then '×' 
            when COALESCE(s3.table_name,'tzqnull') = 'tzqnull' and ((select count(*) from s3 where s3.comp_task_name = t.comp_task_name) = 0) then null else '√' end as table_name3
      ,case when COALESCE(s4.table_name,'tzqnull') = 'tzqnull' and ((select count(*) from s4 where s4.comp_task_name = t.comp_task_name) > 0) then '×' 
            when COALESCE(s4.table_name,'tzqnull') = 'tzqnull' and ((select count(*) from s4 where s4.comp_task_name = t.comp_task_name) = 0) then null else '√' end as table_name4
      ,case when COALESCE(s5.table_name,'tzqnull') = 'tzqnull' and ((select count(*) from s5 where s5.comp_task_name = t.comp_task_name) > 0) then '×' 
            when COALESCE(s5.table_name,'tzqnull') = 'tzqnull' and ((select count(*) from s5 where s5.comp_task_name = t.comp_task_name) = 0) then null else '√' end as table_name5
      ,case when COALESCE(s6.table_name,'tzqnull') = 'tzqnull' and ((select count(*) from s6 where s6.comp_task_name = t.comp_task_name) > 0) then '×' 
            when COALESCE(s6.table_name,'tzqnull') = 'tzqnull' and ((select count(*) from s6 where s6.comp_task_name = t.comp_task_name) = 0) then null else '√' end as table_name6
      ,case when COALESCE(s7.table_name,'tzqnull') = 'tzqnull' and ((select count(*) from s7 where s7.comp_task_name = t.comp_task_name) > 0) then '×' 
            when COALESCE(s7.table_name,'tzqnull') = 'tzqnull' and ((select count(*) from s7 where s7.comp_task_name = t.comp_task_name) = 0) then null else '√' end as table_name7
  from tab_base_info_tmp t
  inner join constant c on 1=1 and c.comp_task_name = t.comp_task_name
  left join s1 on s1.schema = t.schema and s1.table_name = t.table_name and s1.comp_task_name = t.comp_task_name
  left join s2 on s2.schema = t.schema and s2.table_name = t.table_name and s2.comp_task_name = t.comp_task_name
  left join s3 on s3.schema = t.schema and s3.table_name = t.table_name and s3.comp_task_name = t.comp_task_name
  left join s4 on s4.schema = t.schema and s4.table_name = t.table_name and s4.comp_task_name = t.comp_task_name
  left join s5 on s5.schema = t.schema and s5.table_name = t.table_name and s5.comp_task_name = t.comp_task_name
  left join s6 on s6.schema = t.schema and s6.table_name = t.table_name and s6.comp_task_name = t.comp_task_name
  left join s7 on s7.schema = t.schema and s7.table_name = t.table_name and s7.comp_task_name = t.comp_task_name)
select tb.comp_task_id
      ,tb.comp_task_name
      ,tb.schema
      ,tb.table_name
      ,tb.table_name_check_result
      ,tb.table_name1
      ,tb.table_name2
      ,case
         when tb.table_name_check_result = '×' AND tb.table_name1 = '√' AND tb.table_name2 = '×'
           then td.table_define
         when tb.table_name_check_result = '×' AND tb.table_name1 = '×' AND tb.table_name2 = '√'
           then 'drop table if exists ' || tb.table_name || ';'
         else NULL
       end as compare_scripts_of_source2
      ,tb.table_name3
      ,case
         when tb.table_name3 is null then null
         when tb.table_name_check_result = '×' AND tb.table_name1 = '√' AND tb.table_name3 = '×'
           then td.table_define
         when tb.table_name_check_result = '×' AND tb.table_name1 = '×' AND tb.table_name3 = '√'
           then 'drop table if exists ' || tb.table_name || ';'
         else NULL
       end as compare_scripts_of_source3
      ,tb.table_name4
      ,case
         when tb.table_name4 is null then null
         when tb.table_name_check_result = '×' AND tb.table_name1 = '√' AND tb.table_name4 = '×'
           then td.table_define
         when tb.table_name_check_result = '×' AND tb.table_name1 = '×' AND tb.table_name4 = '√'
           then 'drop table if exists ' || tb.table_name || ';'
         else NULL
       end as compare_scripts_of_source4
      ,tb.table_name5
      ,case
         when tb.table_name5 is null then null
         when tb.table_name_check_result = '×' AND tb.table_name1 = '√' AND tb.table_name5 = '×'
           then td.table_define
         when tb.table_name_check_result = '×' AND tb.table_name1 = '×' AND tb.table_name5 = '√'
           then 'drop table if exists ' || tb.table_name || ';'
         else NULL
       end as compare_scripts_of_source5
      ,tb.table_name6
      ,case
         when tb.table_name6 is null then null
         when tb.table_name_check_result = '×' AND tb.table_name1 = '√' AND tb.table_name6 = '×'
           then td.table_define
         when tb.table_name_check_result = '×' AND tb.table_name1 = '×' AND tb.table_name6 = '√'
           then 'drop table if exists ' || tb.table_name || ';'
         else NULL
       end as compare_scripts_of_source6
      ,tb.table_name7
      ,case
         when tb.table_name7 is null then null
         when tb.table_name_check_result = '×' AND tb.table_name1 = '√' AND tb.table_name7 = '×'
           then td.table_define
         when tb.table_name_check_result = '×' AND tb.table_name1 = '×' AND tb.table_name7 = '√'
           then 'drop table if exists ' || tb.table_name || ';'
         else NULL
       end as compare_scripts_of_source7
      -- ,tb.*
  from tab_base_info tb
  left join constant c on c.comp_task_id = tb.comp_task_id
  left join dc_table_define_t td
    on td.source = c.source_name1
   and td.schema = tb.schema
   and td.table_name = tb.table_name
 where 1=1
   and tb.comp_task_id = :id
            """)
        db.session.execute(v_sql, {
            'id': id
        })
        db.session.commit()
        return jsonify({"code": 1, "msg": "【OK】"})
    except Exception as e:
        db.session.rollback()
        return jsonify({"code": 0, "msg": str(e), "error": str(e)}), 500
